Option Explicit On

Imports System.Text



Public Class CommonSQL

    Public Enum AddressTypes
        GV = 1
        Client = 2
        System = 3
    End Enum

    Public Enum EventStatus
        SingleEvent = 1
        Task = 2
    End Enum

    Public Structure AccountsSummary
        Public QuoteNet As Decimal
        Public QuoteGross As Decimal
        Public InvoicedNet As Decimal
        Public InvoicedGross As Decimal
        Public Payments As Decimal
        Public Balance As Decimal
    End Structure

    Public Structure DocumentsSummary
        Public Required As Integer
        Public Received As Integer
        Public InDocStore As Integer
    End Structure


    Public Structure CorporateContact
        Public ContactID As Long
        Public ContactName As String
    End Structure

    Public Structure EmailTemplate
        Public Subject As String
        Public Body As String
    End Structure

    Public Enum EventTypes
        NotSet = 0
        EventCase = 1
        EventPerson = 2
        EventCompany = 3
        EventUserTask = 4
    End Enum

    Public Enum CompanyPeopleView
        All = 1
        Candidates = 2
        Contacts = 3
        Category = 4
    End Enum

    Public Enum CompanyRoles
        Candidate = 1
        Employee = 4
        ExEmployee = 5
        Director = 6
        Contact = 7
        HRDirector = 8
        HRManager = 9
        HRAssistant = 10
        HRGeneralist = 11
        FinanceManager = 12
        VicePresident = 13
    End Enum

    Public Enum AdvertLinkType
        Person = 0
        Company = 1
    End Enum

    Public Structure ConsultantInfo
        Public ConId As Integer
        Public ConsultantName As String
        Public DDI As String
        Public DDIUK As String
        Public Fax As String
        Public JobTitle As String
        Public Accreditation As String
        Public Dislaimer As Integer
        Public Email As String
        Public DepartmentId As Integer
        Public SupervisorId As Integer
        Public OISCId As Integer
        Public DisclaimerId As Integer
        Public StatusId As Integer
        Public NTUser As String
        Public BranchID As Integer
        Public AccessLevel As Integer
    End Structure

    Public Shared Function GetConsultantInfo(ByVal ConId As Integer) As ConsultantInfo

        Dim SQL As New StringBuilder
        Dim myData As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        GetConsultantInfo = Nothing

        With SQL
            .Append("Select Con_Name, Con_DDI, Con_DDIUK, Con_Fax, Con_Jobtitle, Con_Accreditation, Con_DisclaimerID,")
            .Append("con_email,con_department,con_supervisorid,con_oisc,con_disclaimerid,")
            .Append("con_status,con_ntusername, con_BranchID,Con_SecurityLevel ")
            .Append(" from L_Consultant where Con_ID = " & ConId)
        End With

        myData = DB.GetSQLDataTable(SQL.ToString)

        If Not IsNothing(myData) Then
            If myData.Rows.Count > 0 Then
                Dim myCon As New ConsultantInfo

                With myCon
                    .ConId = ConId
                    .ConsultantName = IIf(IsDBNull(myData.Rows(0).Item("con_name")), "", myData.Rows(0).Item("con_name"))
                    .DDI = IIf(IsDBNull(myData.Rows(0).Item("con_ddi")), "", myData.Rows(0).Item("con_ddi"))
                    .DDIUK = IIf(IsDBNull(myData.Rows(0).Item("con_ddiuk")), "", myData.Rows(0).Item("con_ddiuk"))
                    .Fax = IIf(IsDBNull(myData.Rows(0).Item("con_fax")), "", myData.Rows(0).Item("con_fax"))
                    .JobTitle = IIf(IsDBNull(myData.Rows(0).Item("con_jobtitle")), "", myData.Rows(0).Item("con_jobtitle"))
                    .Accreditation = IIf(IsDBNull(myData.Rows(0).Item("con_accreditation")), "", myData.Rows(0).Item("con_accreditation"))
                    .Dislaimer = myData.Rows(0).Item("con_disclaimerid")
                    .Email = IIf(IsDBNull(myData.Rows(0).Item("con_email")), "", myData.Rows(0).Item("con_email"))
                    .DepartmentId = myData.Rows(0).Item("con_department")
                    .SupervisorId = myData.Rows(0).Item("con_supervisorid")
                    .OISCId = myData.Rows(0).Item("con_oisc")
                    .DisclaimerId = myData.Rows(0).Item("con_disclaimerid")
                    .StatusId = myData.Rows(0).Item("con_status")
                    .NTUser = IIf(IsDBNull(myData.Rows(0).Item("con_ntusername")), "", myData.Rows(0).Item("con_ntusername"))
                    .BranchID = myData.Rows(0).Item("con_BranchID")
                    .AccessLevel = myData.Rows(0).Item("Con_SecurityLevel")
                End With

                Return myCon
            End If
        End If

    End Function
    Public Shared Function GetControlGroups(ByVal ControlGroup As Integer) As DataTable
        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        SQL.Append("Select ControlGroup_Table, ControlGroup_TableName, ControlGroup_Name, ControlGroup_IDName, ControlGroup_Formname, ControlGroup_Usage from I_ControlGroups where ControlGroup_ID = " & ControlGroup)
        Return DB.GetSQLDataTable(SQL.ToString)

    End Function

    Public Shared Function GetConsultantNameNT() As String
        ' get consultant name from DB using their user logon
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As New StringBuilder

        With SQL

            'Force my PC at home to use my work user name

            If Environment.MachineName = "MONSTER" Then
                .Append("select con_name from l_consultant where con_ntusername = 'marting'")
            Else
                .Append("select con_name from l_consultant where con_ntusername = '" & Environment.UserName & "'")
            End If

            Return DB.GetSQLScalar(.ToString)

        End With

    End Function

    Public Shared Function GetLoggedOnConsultantID() As Integer
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return (DB.GetSQLScalar("select con_id from l_consultant where con_ntusername = '" & Environment.UserName & "'"))
    End Function

    Public Shared Function GetLoggedOnConsultantBranchID() As Integer
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return (DB.GetSQLScalar("select con_branchid from l_consultant left join l_Departments on L_Consultant.COn_Department = L_Departments.Department_BranchID where con_ntusername =  '" & Environment.UserName & "'"))
    End Function
    Public Shared Function GetBranchID(ByVal BranchId As Integer) As Integer
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return (DB.GetSQLScalar("select con_branchid from l_consultant where con_id =  '" & BranchId & "'"))
    End Function
    Public Shared Function GetLoggedOnConsultantDeptID() As Integer
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return (DB.GetSQLScalar("select con_Department from l_consultant where con_ntusername =  '" & Environment.UserName & "'"))
    End Function

    Public Shared Function GetOption(ByVal strOption) As String
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLScalar("Select Option_Value from L_Options where Option_Text='" & strOption & "'")
    End Function


    Public Shared Function GetConsultantName(ByVal ConsultantId As Integer) As String

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select con_name from l_consultant where con_id = " & ConsultantId)
            Return DB.GetSQLScalar(.ToString)
        End With

    End Function

    Public Shared Function GetCandidateCompanyID(ByVal PersonID As Long) As Long

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select person_companyid from d_person where person_id = " & PersonID)
            Return DB.GetSQLScalar(.ToString)
        End With


    End Function

    Public Shared Function GetCandidateName(ByVal PersonId As Long) As String

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select person_fname + ' ' + person_lname as fullname from d_person ")
            .Append("where person_id = " & PersonId)
            Return DB.GetSQLScalar(.ToString)
        End With

    End Function

    Public Shared Function GetDepartmentName() As String
        ' get Department name for current user

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select department_name from l_departments d, l_consultant c ")
            .Append("where c.con_department = d.department_id ")
            If Environment.MachineName = "MONSTER" Then
                .Append("and con_ntusername = 'marting'")
            Else
                .Append("and con_ntusername = '" & Environment.UserName & "'")
            End If
            Return DB.GetSQLScalar(.ToString)
        End With

    End Function

    Public Shared Function GetLoggedOnConsultantAsList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return (DB.GetSQLDataTable("select con_name, con_id from l_consultant where con_ntusername = '" & Environment.UserName & "'"))
    End Function

    Public Shared Function ConsultantList(Optional ByVal ActiveOnly As Boolean = True, Optional ByVal DepartmentId As Integer = 0, Optional ByVal ConsultantID As Integer = -1, Optional ByVal intBranch As Integer = -1) As DataTable
        ' get list of Consultants

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select con_id, con_name, con_status from l_consultant")
            If ActiveOnly Then
                .Append(" where ( con_status = 1 ")

                If intBranch > -1 Then
                    .Append(" and con_BranchID = " & CStr(intBranch))
                End If

                If DepartmentId > 0 Then
                    .Append(" and con_department = " & DepartmentId)
                End If

                .Append(" ) ")


                If ConsultantID > 0 Then
                    .Append("or Con_id = " & CStr(ConsultantID))
                End If
            End If
            .Append(" order by con_name")
            Return DB.GetSQLDataTable(.ToString)
        End With

    End Function
    Public Shared Function CreditControllerList(Optional ByVal ActiveOnly As Boolean = True, Optional ByVal DepartmentId As Integer = 0, Optional ByVal ConsultantID As Integer = -1, Optional ByVal intBranch As Integer = -1) As DataTable
        ' get list of Consultants

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select con_id, con_name, con_status from l_consultant where con_iscreditcontroller >= 1")
            Return DB.GetSQLDataTable(.ToString)
        End With

    End Function
    Public Shared Function LoadCaseStages(ByVal caseid As Long) As DataTable
        ' get list of Consultants

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL

            .Append("SELECT DISTINCT TOP 100 PERCENT DBo.L_StageLinks.StageLink_ToID as ID, DBo.L_StageLinks.StageLink_Desc as Descr ")
            .Append(" FROM DBo.L_StageLinks INNER JOIN")
            .Append(" DBo.L_Products ON DBo.L_StageLinks.StageLink_DiagramID = DBo.L_Products.Product_StageDiagramID INNER JOIN")
            .Append(" DBo.D_Case ON DBo.L_Products.Product_ID = DBo.D_Case.Case_ProductID")
            .Append(" WHERE(DBo.D_Case.Case_ID = " & caseid & ")")
            .Append(" ORDER BY DBo.L_StageLinks.StageLink_Desc")

            Return DB.GetSQLDataTable(.ToString)
        End With

    End Function
    Public Shared Function LoadPaymentMode() As DataTable
        ' get list of Consultants

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select PaymentP_id as id,PaymentP_Name as descr from l_PaymentPlans where PaymentP_Active=1")
            Return DB.GetSQLDataTable(.ToString)
        End With

    End Function
    Public Shared Function GetAddressText(ByVal AddressLinkId As Long) As String

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            '.Append("select address_text,address_postcode from d_address where address_id = " & AddressId)
            .Append("select a.address_text,address_postcode from d_addresslink l, d_address a where l.addresslink_id = " & AddressLinkId)
            .Append(" and l.addresslink_addressid = a.address_id")

            Return DB.GetSQLScalar(.ToString)
        End With

    End Function

    Public Shared Function GetCaseCandidateID(ByVal CaseID As Long) As Long

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select case_candidateid from d_case where case_id = " & CaseID)
            Return DB.GetSQLScalar(.ToString)
        End With

    End Function


    Public Shared Function GetAccountsSummary(ByVal CaseID As Long) As AccountsSummary

        Dim SQL As New StringBuilder
        Dim myData As DataTable
        Dim mySummary As New AccountsSummary
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        GetAccountsSummary = Nothing

        If CaseID > 0 Then

            'Get quote amounts (charges that have not been invoiced)
            With SQL
                .Append("select coalesce(sum(charge_amount),0) as quotenet, coalesce(sum(charge_amount +( charge_amount * (charge_vat/100))),0) as quotegross from a_charges ")
                .Append("where charge_caseid = " & CaseID & " and charge_invoiceid = 0")
                myData = DB.GetSQLDataTable(.ToString)
            End With

            If Not IsNothing(myData) Then
                If myData.Rows.Count > 0 Then
                    mySummary.QuoteNet = myData.Rows(0).Item("quotenet")
                    mySummary.QuoteGross = myData.Rows(0).Item("quotegross")
                End If
            End If

            myData.Dispose()

            SQL = New StringBuilder

            With SQL
                .Append("select coalesce(sum(charge_amount),0) as invoicenet, coalesce(sum(charge_amount +( charge_amount * (charge_vat/100))),0) as invoicegross from a_charges ")
                .Append("where charge_caseid = " & CaseID & " and charge_invoiceid > 0")
                myData = DB.GetSQLDataTable(.ToString)
            End With

            If Not IsNothing(myData) Then
                If myData.Rows.Count > 0 Then
                    mySummary.InvoicedNet = myData.Rows(0).Item("invoicenet")
                    mySummary.InvoicedGross = myData.Rows(0).Item("invoicegross")
                End If
            End If

            SQL = New StringBuilder

            With SQL
                .Append("select coalesce(sum(payment_amount),0) from a_payments where payment_caseid = " & CaseID)
                mySummary.Payments = DB.GetSQLScalar(.ToString)
            End With

            myData.Dispose()

            mySummary.Balance = mySummary.InvoicedGross - mySummary.Payments

            Return mySummary
        End If

    End Function

    Public Shared Function GetDocStoreSummary(ByVal CaseID As Long) As DocumentsSummary

        Dim SQL As New StringBuilder
        Dim myDocs As New DocumentsSummary
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        If CaseID > 0 Then

            With SQL
                .Append("select count(*) from d_docreqs where docreq_caseid = " & CaseID)
                myDocs.Required = DB.GetSQLScalar(.ToString)
            End With

            SQL = New StringBuilder

            With SQL
                .Append("select count(*) from d_docreqs where docreq_caseid = " & CaseID & " and docreq_received = 1")
                myDocs.Received = DB.GetSQLScalar(.ToString)
            End With

            SQL = New StringBuilder

            With SQL
                .Append("select count(*) from d_docstore where docstore_caseid = " & CaseID)
                myDocs.InDocStore = DB.GetSQLScalar(.ToString)
            End With
        End If

        Return myDocs

    End Function

    Public Shared Function GetPhoneNumbersSQL(ByVal PersonID As Long) As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("select phonenumber_id,phonenumber_name,phonenumber_number,case phonenumber_type  when 1 then 'Main' when 2 then 'Fax' when 3 then 'Both' else '' end AS Main ")
            .Append("from d_phonenumber where phonenumber_personid = " & PersonID)
            .Append(" Order By PhoneNumber_Name")
            Return .ToString
        End With

    End Function

    Public Shared Function GetEmailAddressSQL(ByVal PersonID As Long) As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT EMailAddress_ID ,EMailAddress_Name , EMailAddress_Address , case EMailAddress_type when 1 then 'Main' else '' end AS Main ")
            .Append("FROM D_EmailAddress ")
            .Append("WHERE EMailAddress_PersonID = " & PersonID)
            .Append(" Order By EmailAddress_Name")
            Return .ToString
        End With

    End Function

    Public Shared Function GetPersonAddressSQL(ByVal PersonId As Long) As String

        Dim SQL As New StringBuilder

        With SQL

            .Append("SELECT AddressLink_ID, AddressLink_Name, Case Addresslink_Owner When 0 then 'Co' when 1 then '' end as Owner, ")
            .Append("CASE AddressLink_Type WHEN 1 THEN 'Cor' WHEN 2 THEN 'Inv' WHEN 3 THEN 'Both' ELSE '' END as Type, ")
            .Append("left(D_Address.Address_Text,30) as FirstLine FROM D_AddressLink INNER JOIN D_Address ON D_AddressLink.AddressLink_AddressID = D_Address.Address_ID ")
            .Append("WHERE (AddressLink_PersonID = " & PersonId & ") Order By AddressLink_Name")
            Return .ToString
        End With

    End Function

    Public Shared Function GetEventsSQL(ByVal ID As Long, ByVal ConId As Integer, ByVal EventType As EventTypes) As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("select e.event_id,e.event_dtentered,e.event_description,c.con_name ")
            .Append("from d_event e, l_consultant c ")
            .Append("where e.event_entereDBy = c.con_id ")
            .Append("and e.event_entereDBy = " & ConId)
            .Append(" and e.event_linkid = " & ID)
            .Append(" order by e.event_dtentered desc")
            Return .ToString
        End With

    End Function

    Public Shared Function GetEventMessage(ByVal EventID As Long) As String


        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select event_message from d_event where event_id = " & EventID)
            Return DB.GetSQLScalar(.ToString)
        End With

    End Function

    Public Shared Function GetWhereHeard(ByVal LinkId As Long, ByVal LinkType As AdvertLinkType) As String

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select advertising_wherehear from d_advertising where advertising_linkid = " & LinkId)
            .Append(" and Advertising_LinkType = " & LinkType)
            Return DB.GetSQLScalar(.ToString)
        End With

    End Function

    Public Shared Function GetPersonName(ByVal PersonId As Long) As String

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select person_fname + ' ' + person_lname as fullname from d_person ")
            .Append("where person_id = " & PersonId)
        End With

        Return DB.GetSQLScalar(SQL.ToString)

    End Function


    Public Overloads Shared Function SearchCompany(ByVal CompanyName As String) As String

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("SELECT Company_Id, Company_Name, Company_ParentID  FROM D_Company ")
            .Append("WHERE Company_name Like '%" & CompanyName & "%'")
        End With

        Return SQL.ToString

    End Function

    Public Overloads Shared Function SearchCompany(ByVal CompanyId As Long) As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT Company_Id, Company_Name, Company_ParentID  FROM D_Company ")
            .Append("WHERE Company_id = " & CompanyId)
        End With

        Return SQL.ToString

    End Function




    Public Shared Function GetCorporatePeople(ByVal CompanyId As Long, Optional ByVal RoleId As CompanyPeopleView = CompanyPeopleView.All, Optional ByVal RoleCategory As CompanyRoles = 0) As String

        'Gets a list of all people associated with a company client

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT D_Person.Person_ID, D_Person.Person_FName + ' ' + D_Person.Person_LName AS Name, L_Roles.Role_Name ")
            .Append("FROM L_Roles INNER JOIN D_Person ON L_Roles.Role_ID = D_Person.Person_CompanyRoleID ")
            .Append("WHERE (D_Person.Person_CompanyID = " & CompanyId & ") ")

            Select Case RoleId
                Case CompanyPeopleView.Candidates
                    .Append("and person_companyroleid = " & CompanyRoles.Candidate & " ")

                Case CompanyPeopleView.Category

                    .Append("and person_companyroleid = " & RoleCategory & " ")


                Case CompanyPeopleView.Contacts
                    .Append("and person_companyroleid = " & CompanyRoles.Contact & " ")

            End Select

            .Append("ORDER BY  Person_Fname, Person_LName")
            Return .ToString
        End With

    End Function

    Public Shared Function GetCorporateContact(ByVal CaseID As Long) As CorporateContact

        Dim SQL As New StringBuilder
        Dim myData As New DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        GetCorporateContact = Nothing

        With SQL
            .Append("select p.person_id,p.person_fname + ' ' + p.person_lname as contactname ")
            .Append("from d_person p, d_case c ")
            .Append("where c.case_id = " & CaseID)
            .Append(" and c.case_contactid = p.person_id and c.case_companyid > 0")
            myData = DB.GetSQLDataTable(.ToString)

            If Not IsNothing(myData) Then
                If myData.Rows.Count > 0 Then
                    Dim myInfo As CorporateContact
                    Dim myRow As DataRow = myData.Rows(0)

                    With myInfo
                        .ContactID = myRow.Item("person_id")
                        .ContactName = IIf(IsDBNull(myRow.Item("contactname")), "", myRow.Item("contactname"))
                    End With
                    Return myInfo
                End If
            End If

        End With

    End Function

    Public Shared Function GetCompanyWhereHeard(ByVal CompanyId As Long) As String

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("Select top 1 advertising_wherehear from D_Advertising  Where Advertising_LinkType = 1 AND Advertising_LinkID = " & CompanyId)
            .Append(" order by advertising_dtentered")
            Return DB.GetSQLScalar(.ToString)
        End With

    End Function

    Public Shared Function GetCorporateContacts(ByVal CompanyID As Long) As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT D_Person.Person_ID, D_Person.Person_FName + ' ' + D_Person.Person_LName AS Name, L_Roles.Role_Name FROM L_Roles INNER JOIN D_Person ON L_Roles.Role_ID = D_Person.Person_CompanyRoleID WHERE (L_Roles.Role_Employee = 1 OR L_Roles.Role_Employee = 2) AND (D_Person.Person_CompanyID = " & CompanyID & ")")
            Return .ToString
        End With
    End Function

    Public Shared Function GetCorporateAddresses(ByVal CompanyId As Long) As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT AddressLink_ID, AddressLink_Name, CASE AddressLink_Type WHEN 1 THEN 'Cor' WHEN 2 THEN 'Inv' WHEN 3 THEN 'Both' ELSE '' END as Type, left(D_Address.Address_Text,30) as FirstLine, AddressLink_Type FROM D_AddressLink INNER JOIN D_Address ON D_AddressLink.AddressLink_AddressID = D_Address.Address_ID WHERE (AddressLink_CompanyID = " & CompanyId & ")")
            '.Append("SELECT AddressLink_ID, AddressLink_Name, CASE AddressLink_Type WHEN 1 THEN 'Cor' WHEN 2 THEN 'Inv' WHEN 3 THEN 'Both' ELSE '' END as Type, left(D_Address.Address_Text,30) as FirstLine FROM D_AddressLink INNER JOIN D_Address ON D_AddressLink.AddressLink_AddressID = D_Address.Address_ID WHERE (AddressLink_CompanyID = " & CompanyId & ")")
            Return .ToString
        End With

    End Function

#Region "Lookups"



    Public Shared Function ProductListByType(ByVal ProductType As Integer, ByVal ProductID As Integer) As DataTable

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select product_id,product_name from l_products where product_type = " & ProductType)
            If ProductID = -1 Then
                .Append(" and (Product_DTDisabled is null ) ")
            Else
                .Append(" and (Product_ID = " & CStr(ProductID) & " or Product_DTDisabled is null ) ")
            End If
            .Append(" order by product_name")
            Return DB.GetSQLDataTable(.ToString)
        End With

    End Function


    Public Shared Function DepartmentList(Optional ByVal intBranchID As Integer = -1) As DataTable
        ' get list of departments

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            If intBranchID = -1 Then
                Return DB.GetSQLDataTable("Select Department_ID, Department_Name from L_Departments order by Department_name")
            Else
                Return DB.GetSQLDataTable("Select Department_ID, Department_Name from L_Departments where Department_BranchID = " & CStr(intBranchID) & "  order by Department_name ")
            End If
        End With

    End Function
    Public Shared Function DocCommentList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLDataTable("Select DocComment_Id,DocComment_Msg from L_DocComments")
    End Function
    Public Shared Function BranchList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLDataTable("Select Branch_ID, Branch_Name from l_Branch")
    End Function
    Public Shared Function BranchListForBranch() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLDataTable("Select Branch_ID, Branch_Name from l_Branch where branch_id =" & GlobalCRM.CurrentBranchId)
    End Function
    Public Shared Function SecurityList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLDataTable("Select Security_ID, Security_Name from l_Security")
    End Function

    Public Shared Function DepartmentListWithTopRow(Optional ByVal strTopRow As String = "All") As DataTable
        ' get list of departments plus put "All" at top of list with ID value of 0

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("Select 0 as 'Department_ID','" & strTopRow & "' as Department_Name, 'AAA' As SortOrder From L_Departments")
            .Append(" Union ")
            .Append("Select Department_ID, Department_Name, Department_Name as SortOrder from L_Departments order by SortOrder")
            Return DB.GetSQLDataTable(.ToString)
        End With

    End Function

    Public Shared Function AddExtraRowToDataTable(ByVal dtbMyTable As DataTable, ByVal intValue As Integer, Optional ByVal strText As String = "All") As DataTable
        ' use to add new row to an existing datatable, useful for having a select all option at top of a drop down list

        Dim dtrRow As DataRow
        dtrRow = dtbMyTable.NewRow

        dtrRow.Item(0) = intValue
        dtrRow.Item(1) = strText

        ' add at top of table
        dtbMyTable.Rows.InsertAt(dtrRow, 0)

        AddExtraRowToDataTable = dtbMyTable

    End Function

    Public Shared Function IndustryList() As DataTable

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select industry_id,industry_name from l_industry order by industry_name")
            Return DB.GetSQLDataTable(.ToString)
        End With

    End Function

    Public Shared Function CorporateContactList(ByVal CompanyId As Long) As DataTable

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("Select Person_ID, Person_Fname + ' ' + Person_LName as PersonName From D_Person INNER JOIN L_Roles ON D_Person.Person_CompanyRoleID = L_Roles.Role_ID WHERE Person_CompanyID = " & CompanyId & " AND Role_Employee in (1,2) ")
            Return DB.GetSQLDataTable(.ToString)
        End With

    End Function

    Public Shared Function NationalityList() As DataTable

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select country_id,country_nationality from l_country order by country_nationality")
            Return DB.GetSQLDataTable(.ToString)
        End With

    End Function

    Public Shared Function RoleList() As DataTable

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select role_id,role_name from l_roles order by role_name")
            Return DB.GetSQLDataTable(.ToString)
        End With

    End Function

    'Public Shared Function WhereHeardList() As DataTable

    '    Dim SQL As New StringBuilder

    '    With SQL
    '        .Append("select wherehear_id,wherehear_text from l_wherehear order by wherehear_text")
    '        Return DB.GetSQLDataTable(.ToString)
    '    End With

    'End Function

    Public Shared Function QualificationList() As DataTable

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select qual_id,qual_name from l_qualifications order by qual_name")
            Return DB.GetSQLDataTable(.ToString)
        End With

    End Function

    Public Shared Function CountryList() As DataTable

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select country_id,country_name from l_country order by country_name")
            Return DB.GetSQLDataTable(.ToString)
        End With

    End Function

    Public Shared Function CountyList() As DataTable

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select county_id,county_name from l_county order by county_name")
            Return DB.GetSQLDataTable(.ToString)
        End With

    End Function


    Public Shared Function UKVisaList() As DataTable

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select ukvisa_id,ukvisa_name from l_ukvisa order by ukvisa_name")
            Return DB.GetSQLDataTable(.ToString)
        End With

    End Function

    Public Shared Function JobTitleList() As DataTable

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select jobtitle_id,jobtitle_name from l_jobtitles order by jobtitle_name")
            Return DB.GetSQLDataTable(.ToString)
        End With

    End Function


    Public Shared Function CompanySizeList() As DataTable

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select companysize_id,companysize_name + ' (' + companysize_desc + ')' as description from l_companysize order by companysize_order")
            Return DB.GetSQLDataTable(.ToString)
        End With

    End Function

    Public Shared Function WhereHearList() As DataTable

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select wherehear_id,wherehear_text from l_wherehear order by wherehear_text")
            Return DB.GetSQLDataTable(.ToString)
        End With

    End Function

    Public Shared Function TitleList(Optional ByVal StrTitle As String = "") As DataTable

        Dim SQL As New StringBuilder
        Dim objDT As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            If StrTitle <> "" Then
                .Append("select title_id,title_text, title_discontinued from l_titles where Title_Discontinued = 0 or Title_Text='" & StrTitle & "' order by title_text")
            Else
                .Append("select title_id,title_text, title_discontinued from l_titles where Title_Discontinued = 0 order by title_text")
            End If
            objDT = DB.GetSQLDataTable(.ToString)
            'objDT = CommonFunc.FindDiscontinued(objDT, "title_text", "title_discontinued")
            Return objDT
        End With

    End Function

    Public Shared Function AccountTypeList() As DataTable

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select accounttype_id,accounttype_name from l_accounttypes order by accounttype_name")
            Return DB.GetSQLDataTable(.ToString)
        End With

    End Function

    Public Shared Function CompanyStatusList() As DataTable

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select companystatus_id,companystatus_name from l_companystatus order by companystatus_name")
            Return DB.GetSQLDataTable(.ToString)
        End With

    End Function

   

    Public Shared Function CompanyRoleList() As DataTable

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select role_id,role_name from l_roles order by role_name")
            Return DB.GetSQLDataTable(.ToString)
        End With

    End Function

    Public Shared Function GetPipeline(ByVal ConId As Integer) As String
        ' get Pipeline message displayed at bottom of frmMainForm

        Dim SQL As New StringBuilder
        Dim myData As DataTable
        Dim Msg As New StringBuilder
        Dim myRow As DataRow
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        ' get number of active leads and leads on hold
        With SQL
            .Append("Select Count(Case_ID) as Leads, coalesce(Sum (Case_activeStatus),0) as LeadOnHold")
            .Append(" From D_Case")
            .Append(" Where Case_StatusId < 100")
            .Append(" And Case_ConSaleId = " & ConId)
        End With

        myData = DB.GetSQLDataTable(SQL.ToString)

        If Not IsNothing(myData) Then
            If myData.Rows.Count > 0 Then

                myRow = myData.Rows(0)

                With Msg

                    .Append(myRow("Leads") & " Leads ")
                    If myRow("leadonhold") > 0 Then
                        .Append("(" & myRow("leadonhold") & " on Hold)")
                    End If

                    .Append(" : ")

                End With

            End If
        End If

        ' get number of sold and open cases
        SQL = New StringBuilder

        With SQL
            .Append("Select Count(Case_ID) as Cases, coalesce(Sum (Case_activeStatus),0) as CaseOnHold")
            .Append(" From D_Case")
            .Append(" Where Case_StatusId between 100 and 999")
            .Append(" And Case_ConCWID = " & ConId)
        End With


        myData = DB.GetSQLDataTable(SQL.ToString)

        If Not IsNothing(myData) Then
            If myData.Rows.Count > 0 Then
                myRow = myData.Rows(0)

                With Msg

                    .Append(myRow("cases") & " Initiated cases ")
                    If myRow("caseonhold") > 0 Then
                        .Append("(" & myRow("caseonhold") & " on Hold)")
                    End If

                End With

            End If
        End If

        ' get Web Loop Score for selected consultant (uses SP_ConPoints stored procedure)
        SQL = New StringBuilder

        With SQL
            .Append("Declare @Pts integer" & vbCrLf)
            .Append("Declare @Nothing integer" & vbCrLf & vbCrLf)
            .Append("Exec SP_ConPoints @ConID = " & ConId & ", @Points = @Pts Output")
            .Append(" , @OverdueTasks = @Nothing  Output, @NoTasks = @Nothing  Output")
            .Append(" , @DaysSinceTask = @Nothing  Output, @NewWebLeads = @Nothing  Output")
            .Append(" , @NewExpLeads = @Nothing  Output, @NewProspect = @Nothing  Output")
            .Append(" , @Benchmark = @Nothing  Output" & vbCrLf & vbCrLf)
            .Append("Select @Pts as ConPoints")
        End With

        myData = Nothing
        'myData = DB.GetSQLDataTable(SQL.ToString)

        If Not IsNothing(myData) Then
            If myData.Rows.Count > 0 Then
                myRow = myData.Rows(0)

                Msg.Append("Web Loop Score: " & myRow("ConPoints"))
            Else
                Msg.Append("Web Loop Score: N/A")
            End If
        End If

        If Not myData Is Nothing Then

            myData.Dispose()
        End If
        Return Msg.ToString


    End Function

    Public Shared Function GetEmailTemplate(ByVal TemplateId As Integer) As EmailTemplate

        Dim myData As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        GetEmailTemplate = Nothing

        myData = DB.GetSQLDataTable("Select EmailTemplate_subjectline, EmailTemplate_Body from D_EmailTemplates where EmailTemplate_id = " & TemplateId)

        If Not IsNothing(myData) Then
            If myData.Rows.Count > 0 Then
                Dim myTemp As New EmailTemplate

                myTemp.Subject = IIf(IsDBNull(myData.Rows(0).Item("emailtemplate_subjectline")), "", myData.Rows(0).Item("emailtemplate_subjectline"))
                myTemp.Body = IIf(IsDBNull(myData.Rows(0).Item("emailtemplate_body")), "", myData.Rows(0).Item("emailtemplate_body"))

                Return myTemp

            End If
        End If
    End Function

    Public Shared Function GetEmailTemplateList(ByVal CaseId As Long, ByVal CompanyId As Long) As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SLSId As Integer = CommonDataFunctions.GblFunc_Get_Dataset(CaseId, 3)
        Dim SQL As New StringBuilder
        Dim IntCo As Integer
        Dim myStage As Cases.CaseStage

        If SLSId = 0 Then
            SLSId = 34
        End If

        If CompanyId > 0 Then
            IntCo = 2
        Else
            IntCo = 1
        End If

        myStage = Cases.GetCaseStage(CaseId)

        If myStage.StageNumber = 0 Then
            myStage.StageNumber = 10
        End If

        With SQL
            .Append("SELECT distinct D_EmailTemplates.EMailTemplate_ID, D_EmailTemplates.EMailTemplate_Name")

            .Append(" FROM D_EmailTemplates INNER JOIN")
            .Append(" I_SL_SLSMailTemplates ON D_EmailTemplates.EMailTemplate_ID = I_SL_SLSMailTemplates.SLSMailTemplate_TemplateID")

            .Append(" WHERE (I_SL_SLSMailTemplates.SLSMailTemplate_SLSID = " & SLSId & ") ")
            .Append(" AND (I_SL_SLSMailTemplates.SLSMailTemplate_StageEnable <= " & myStage.StageNumber & ") ")
            .Append(" AND (I_SL_SLSMailTemplates.SLSMailTemplate_StageDisable > " & myStage.StageNumber & ") ")
            .Append(" AND (I_SL_SLSMailTemplates.SLSMailTemplate_CustomerType IN (0, " & IntCo & "))")

            .Append(" ORDER BY EMailTemplate_Name")
        End With

        Return DB.GetSQLDataTable(SQL.ToString)

    End Function

    Public Shared Function GetDisclaimer(ByVal DisclaimerId As Integer) As String
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim Disclaimer As String = DB.GetSQLScalar("Select disclaimer_text from L_Disclaimer where Disclaimer_ID = " & DisclaimerId)

        If Disclaimer.Trim = "" Then
            Disclaimer = DB.GetSQLScalar("Select disclaimer_text from L_Disclaimer where Disclaimer_ID = 1")
        End If

        If Disclaimer.Trim = "" Then
            Disclaimer = "This e-mail and any file transmitted with it are confidential and may be legally privileged. It is intended solely for the addressee and may not be disclosed to or used by anyone other than the addressee. If you have received this e-mail in error, please advise the sender immediately."
        End If

        Return Disclaimer

    End Function

    Public Shared Function GetAddressBook(ByVal AddressType As AddressTypes) As DataTable

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL

            Select Case AddressType

                Case AddressTypes.GV

                    .Append("Select Con_Name as personname, Con_Email from L_Consultant as EmailAddress")
                    .Append(" WHERE Con_Email <> '' and Con_Status = 1")
                    .Append(" ORDER BY Con_Name")


                Case AddressTypes.Client

                    If GlobalCRM.CurrentCompanyID > 0 Then
                        .Append("SELECT D_Person.Person_FName + ' ' + D_Person.Person_LName AS PersonName, D_EmailAddress.EMailAddress_Address as EmailAddress")
                        .Append(" FROM D_EmailAddress INNER JOIN")
                        .Append(" D_Person ON D_EmailAddress.EMailAddress_PersonID = D_Person.Person_ID")
                        .Append(" WHERE (D_EmailAddress.EMailAddress_Type = 1) And (D_Person.Person_CompanyID = " & GlobalCRM.CurrentCompanyID & ")")
                        .Append(" ORDER BY D_Person.Person_FName + ' ' + D_Person.Person_LName")
                    Else
                        .Append("Select EmailAddress_Name, EMailAddress_Address from D_EmailAddress ")
                        .Append(" WHERE EmailAddress_PersonID = " & GlobalCRM.CurrentCandidateID)
                        .Append(" ORDER BY EMailAddress_Name")
                    End If

                Case AddressTypes.System

                    .Append("Select AddressBook_Name as personname, AddressBook_Address from D_AddressBook")
                    .Append(" ORDER BY AddressBook_Name")

            End Select

        End With

        Return DB.GetSQLDataTable(SQL.ToString)

    End Function

    Public Shared Function RelationshipList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLDataTable("Select Rel_ID, Rel_ChildName, Rel_ParentName from L_Relationships Order By Rel_ChildName")
    End Function

    Public Shared Function SkilledOccupationCategories(ByVal CountryId As Integer) As DataTable

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("SELECT SkilledCategory_ID, SkilledCategory_Name FROM L_SkilledCategories")
            .Append(" WHERE (SkilledCategory_CountryID = " & CountryId & ")")
            .Append(" ORDER BY SkilledCategory_Name")
        End With

        Return DB.GetSQLDataTable(SQL.ToString)

    End Function

    Public Shared Function SkilledOccupationList(ByVal CategoryId As Integer) As DataTable

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("Select SkilledOcc_ID, SkilledOcc_Title from L_SkilledOcc ")
            .Append(" Where SkilledOcc_CategoryID = " & CategoryId)
            .Append(" AND SkilledOcc_Inactive = 0")
            .Append(" Order By SkilledOcc_Title")
        End With

        Return DB.GetSQLDataTable(SQL.ToString)

    End Function

    Public Shared Function GetSkillOccupationCategory(ByVal OccupationId As Integer) As Integer
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLScalar("select skilledocc_categoryid from l_skilledocc where skilledocc_id = " & OccupationId)
    End Function

    Public Shared Function DisclaimerList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLDataTable("select disclaimer_id,disclaimer_text from l_disclaimer")
    End Function

    Public Shared Function OISCList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLDataTable("select oisc_id,oisc_description from l_oisc")
    End Function

    Public Shared Function CaseStageList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLDataTable("SELECT CaseStage_Number, CaseStage_Title FROM L_CaseStages ORDER BY CaseStage_Number")
    End Function

    Public Shared Function GetAdvertDestinations() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLDataTable("Select AdDest_ID, AdDest_Name, AdDestType_Name from L_AdvertDestinations INNER JOIN L_AdDestTypes ON L_AdvertDestinations.AdDest_Type = L_AdDestTypes.AdDestType_ID Order By AdDest_Name")
    End Function

#End Region



End Class
